package com.yeskery.transfer.data;

import com.yeskery.transfer.core.SproutException;
import lombok.Setter;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * 数据操作工具类
 * @author shuangjiang.du
 * @date 2018-12-14 14:25
 * @version 1.0
 */
public class DataKit {

	/** 重音符 */
	private static final String GRAVE_ACCENT = "`";

	/** 逗号 */
	private static final char COMMA = ',';

	/** 数据库操作 */
	private static final String SQL = "SELECT * FROM ";

	/** 数据库条数操作 */
	private static final String COUNT_SQL = "SELECT COUNT(*) FROM ";

	/** 分页操作 */
	private static final String LIMIT = " LIMIT ?,?";

	/** 所有列 */
	private static final String SHOW_ALL_COLUMNS = "SHOW FULL COLUMNS FROM ";

	/** 删除表 */
	private static final String DELETE_TABLE = "DROP TABLE IF EXISTS ";

	@Setter
	private int maxQuerySize = 1000;

	/**
	 * 获取所有的表名
	 * @param connection 连接对象
	 * @param dataResult 数据获取接口
	 * @return 表名集合
	 * @param <T> 查询后的数据类型
	 * @throws SQLException SQLException
	 */
	public <T> List<T> getTableMetaData(Connection connection, DataResult<List<T>> dataResult) throws SQLException {
		//获取数据库的元数据
		DatabaseMetaData databaseMetaData = connection.getMetaData();
		ResultSet resultSet = databaseMetaData.getTables(connection.getCatalog(), null,
				null, new String[] { "TABLE" });
		return dataResult.get(resultSet);
	}

	/**
	 * 获取所有的表名
	 * @param connection 连接对象
	 * @return 表名集合
	 * @throws SQLException SQLException
	 */
	public List<String> getTableNames(Connection connection) throws SQLException {
		return getTableMetaData(connection, resultSet -> {
			List<String> tableNameList = new ArrayList<>();
			while(resultSet.next()) {
				tableNameList.add(resultSet.getString(3));
			}
			return tableNameList;
		});
	}

	/**
	 * 查询指定的表名是否存在
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @return <code>true</code> for exist,else not.
	 * @throws SQLException
	 */
	public boolean existTable(Connection connection, String tableName) throws SQLException {
		List<String> tableNameList = getTableNames(connection);
		if (tableNameList == null || tableNameList.size() < 1) {
			return false;
		}
		return tableNameList.stream().anyMatch(s -> s.equalsIgnoreCase(tableName));
	}

	/**
	 * 获取指定表的元数据
	 * @param connection 连接对象
	 * @param dataResult 数据获取接口
	 * @param tableName 表名
	 * @param <T> 指定转换的类型
	 * @return 元数据集合
	 * @throws SQLException SQLException
	 */
	public <T> List<T> getMetaData(Connection connection, DataResult<List<T>> dataResult,
			String tableName)
			throws SQLException {
		ResultSet resultSet = null;
		try(PreparedStatement pstmt = connection.prepareStatement(
				SHOW_ALL_COLUMNS + GRAVE_ACCENT + tableName + GRAVE_ACCENT)) {
			resultSet = pstmt.executeQuery();
			return dataResult.get(resultSet);
		} finally {
			close(resultSet);
		}
	}

	/**
	 * 获取指定表的元数据
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @return 元数据集合
	 * @throws SQLException SQLException
	 */
	public List<MetaData> getMetaData(Connection connection, String tableName)
			throws SQLException {
		return getMetaData(connection, resultSet -> {
			List<MetaData> tableMetaDataList = new LinkedList<>();
			while (resultSet.next()) {
				MetaData metaData = new MetaData();
				metaData.setName(resultSet.getString("Field"));
				metaData.setType(resultSet.getString("Type"));
				metaData.setComment(resultSet.getString("Comment"));
				metaData.setNullable("YES".equalsIgnoreCase(resultSet.getString("Null")));
				metaData.setAutoIncrement("auto_increment".equalsIgnoreCase(resultSet.getString("Extra")));
				String key = resultSet.getString("Key");
				switch (key) {
					case "PRI" : metaData.setKey(MetaData.Key.PRI);break;
					case "UNI" : metaData.setKey(MetaData.Key.UNI);break;
					case "MUL" : metaData.setKey(MetaData.Key.MUL);break;
					default: metaData.setKey(MetaData.Key.NONE);
				}
				tableMetaDataList.add(metaData);
			}
			return tableMetaDataList;
		} ,tableName);
	}

	/**
	 * 从指定表获取数据
	 * @param connection 连接对象
	 * @param dataResult 数据获取接口
	 * @param tableName 表名
	 * @param page 查询的页码
	 * @param <T> 查询后的数据类型
	 * @return 查询后的数据
	 * @throws SQLException SQLException
	 */
	public <T> List<T> getData(Connection connection, DataResult<List<T>> dataResult, String tableName, int page)
			throws SQLException {
		if (page < 1) {
			page = 1;
		}
		ResultSet resultSet = null;
		try (PreparedStatement pstmt = connection.prepareStatement(
				SQL + GRAVE_ACCENT + tableName + GRAVE_ACCENT + LIMIT)) {
			pstmt.setObject(1, (page - 1) * maxQuerySize);
			pstmt.setObject(2, maxQuerySize);
			resultSet = pstmt.executeQuery();
			return dataResult.get(resultSet);
		} finally {
			close(resultSet);
		}
	}

	/**
	 * 从指定表获取数据
	 * @param connection 连接对象
	 * @param dataResult 数据获取接口
	 * @param tableName 表名
	 * @param <T> 查询后的数据类型
	 * @return 查询后的数据
	 * @throws SQLException SQLException
	 */
	public <T> List<T> getData(Connection connection, DataResult<List<T>> dataResult, String tableName)
			throws SQLException {
		return getData(connection, dataResult, tableName, 1);
	}

	/**
	 * 从指定表获取该表的数据总条数
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @return 数据总条数
	 * @throws SQLException SQLException
	 */
	public Long getDataCount(Connection connection, String tableName) throws SQLException {
		ResultSet resultSet = null;
		try (Statement stmt = connection.createStatement()) {
			resultSet = stmt.executeQuery(COUNT_SQL + GRAVE_ACCENT + tableName + GRAVE_ACCENT);
			return resultSet.next() ? resultSet.getLong(1) : 0L;
		} finally {
			close(resultSet);
		}
	}

	/**
	 * 从指定表获取该表的数据总页数
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @return 数据总页数
	 * @throws SQLException SQLException
	 */
	public Long getDataPages(Connection connection, String tableName) throws SQLException {
		Long dataCount = getDataCount(connection, tableName);
		double count = new Double(dataCount) / maxQuerySize;
		long pages = (long) count;
		return count > pages ? pages + 1 : pages;
	}

	/**
	 * 从指定表获取该表的数据总条数和总页数
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @return 数据总条数和总页数
	 * @throws SQLException SQLException
	 */
	public Page getPage(Connection connection, String tableName) throws SQLException {
		Long dataCount = getDataCount(connection, tableName);
		double count = new Double(dataCount) / maxQuerySize;
		long pages = (long) count;
		return new Page(dataCount, count > pages ? pages + 1 : pages);
	}

	/**
	 * 删除指定的数据库表
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @throws SQLException SQLException
	 */
	public void deleteTable(Connection connection, String tableName) throws SQLException {
		try (PreparedStatement pstmt = connection.prepareStatement(
				DELETE_TABLE + GRAVE_ACCENT + tableName + GRAVE_ACCENT)){
			pstmt.execute();
		}
	}

	/**
	 * 创建指定的数据库表
	 * @param connection 连接对象
	 * @param tableName 表名
	 * @param metaDataList  字段数据集合
	 * @throws SQLException SQLException
	 */
	public void createTable(Connection connection, String tableName, List<MetaData> metaDataList) throws SQLException {
		try (Statement statement = connection.createStatement()) {
			statement.execute(createTableSql(tableName, metaDataList));
		}
	}

	/**
	 * 向指定的数据库连接查询新的数据
	 * @param connection 数据库连接
	 * @param tableName 表名
	 * @param dataList 数据集合
	 * @throws SQLException SQLException
	 */
	public void insertData(Connection connection, String tableName, List<Data> dataList) throws SQLException {
		try (PreparedStatement pstmt = connection.prepareStatement(createInsertSql(tableName, dataList))) {
			for (int i = 1;i <= dataList.size();i++) {
				pstmt.setObject(i, dataList.get(i - 1).getData());
			}
			pstmt.execute();
		}
	}

	/**
	 * 创建插入数据的SQL
	 * @param tableName 表名
	 * @param dataList 数据集合
	 * @return 插入数据的SQL
	 */
	private String createInsertSql(String tableName, List<Data> dataList) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("INSERT INTO ").append(GRAVE_ACCENT).append(tableName).append(GRAVE_ACCENT).append(" (");
		dataList.forEach(data -> sqlBuilder.append(data.getField()).append(COMMA));
		if (sqlBuilder.charAt(sqlBuilder.length() - 1) == COMMA) {
			sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
		}
		sqlBuilder.append(") VALUES (");
		dataList.forEach(data -> sqlBuilder.append("?,"));
		if (sqlBuilder.charAt(sqlBuilder.length() - 1) == COMMA) {
			sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
		}
		sqlBuilder.append(")");
		return sqlBuilder.toString();
	}

	/**
	 * 创建创建表的SQL
	 * @param tableName 表名
	 * @param metaDataList 表的字段信息集合
	 * @return 创建表的SQL
	 */
	private String createTableSql(String tableName, List<MetaData> metaDataList) {
		StringBuilder sqlBuilder = new StringBuilder();
		sqlBuilder.append("CREATE TABLE").append(GRAVE_ACCENT).append(tableName).append(GRAVE_ACCENT).append("(");
		List<MetaData> primaryKeyMetaDataList = new ArrayList<>();
		for (MetaData metaData : metaDataList) {
			if (metaData.getKey() == MetaData.Key.PRI) {
				primaryKeyMetaDataList.add(metaData);
			}
			sqlBuilder.append(GRAVE_ACCENT).append(metaData.getName()).append(GRAVE_ACCENT).append(" ")
					.append(metaData.getType());
			if (!metaData.getNullable()) {
				sqlBuilder.append(" NOT NULL");
			}
			if (metaData.getAutoIncrement()) {
				sqlBuilder.append(" AUTO_INCREMENT");
			}
			if (metaData.getComment() != null && !metaData.getComment().isEmpty()) {
				sqlBuilder.append(" COMMENT '").append(metaData.getComment()).append("'").append(COMMA);
			}
			if (sqlBuilder.charAt(sqlBuilder.length() - 1) != COMMA) {
				sqlBuilder.append(COMMA);
			}
		}
		if (primaryKeyMetaDataList.size() > 0) {
			sqlBuilder.append("PRIMARY KEY (");
			for (MetaData metaData : primaryKeyMetaDataList) {
				sqlBuilder.append(GRAVE_ACCENT).append(metaData.getName()).append(GRAVE_ACCENT).append(COMMA);
			}
			if (sqlBuilder.charAt(sqlBuilder.length() - 1) == COMMA) {
				sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
			}
			sqlBuilder.append(")");
		}
		if (sqlBuilder.charAt(sqlBuilder.length() - 1) == COMMA) {
			sqlBuilder.deleteCharAt(sqlBuilder.length() - 1);
		}
		sqlBuilder.append(")");
		return sqlBuilder.toString();
	}

	/**
	 * 关闭数据库查询结果对象
	 * @param resultSet 数据库查询结果对象
	 */
	private void close(ResultSet resultSet) {
		try {
			if (resultSet != null && !resultSet.isClosed()) {
				resultSet.close();
			}
		} catch (SQLException e) {
			throw new SproutException("Error closing Result.", e);
		}
	}
}
